package com.phospherus.common.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelWriter {
//	public static void main(String[] args) {
//		InputStream is = ExcelWriter.class.getClassLoader().getResourceAsStream("cfg/cangku_huizong.xlsx");
//		Workbook wb = buildWorkbook(is);
//
//		Sheet sheet = wb.getSheetAt(0);
//		System.out.println(sheet);
//		Map<String, Object> formData = new HashMap<>();
//		formData.put("orgName", "丰台综合仓库");
//		writeSheet(sheet, formData);
//
//		List<Object[]> listData = new ArrayList<>();
//		listData.add(new Object[]{"aaa","ddd","ccc"});
//		listData.add(new Object[]{"aaa2","ddd","ccc"});
//		listData.add(new Object[]{"aaa","ddd3","ccc"});
//		writeSheet(wb.getSheetAt(1), listData, 6 ,2);
//
//		File file = new File("D:/storage/仓库汇总.xlsx");
//		storeWorkbook(wb, file);
//	}
	public static Workbook buildWorkbook(InputStream is){
		try {
			Workbook wb = WorkbookFactory.create(is);
			return wb;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			try {
				is.close();
			} catch (IOException e) {
			}
		}
		
	}
	public static void writeSheet(Sheet sheet, Map<String, Object> formData){
		Iterator<Row> rowIter = sheet.iterator();
		while(rowIter.hasNext()){
			 Iterator<Cell> cellIter = rowIter.next().cellIterator();
			 while(cellIter.hasNext()){
				 Cell cell = cellIter.next();
				 writeCell(cell, formData);
			 }
		}
	}
	public static void writeSheet(Sheet sheet, List<Object[]> listData, int fromRowIndex, int fromColIndex){
		List<CellStyle> defaultStyles = new ArrayList<CellStyle>();
		if(listData!=null && listData.size()>0){
			Row templateRow = sheet.getRow(fromRowIndex);
			if(templateRow==null) {
				templateRow = sheet.createRow(fromRowIndex); 
			}
			for(int idx = 0; idx<listData.get(0).length;idx++){
				Cell cell = templateRow.getCell(fromColIndex+idx);
				if(cell==null) {
					cell=templateRow.createCell(fromColIndex+idx);
				}
				defaultStyles.add(templateRow.getCell(fromColIndex+idx).getCellStyle());
			}
		}
		for(int rIdx = 0; rIdx<listData.size(); rIdx++){
			Object[] rowData = listData.get(rIdx);
			Row row = sheet.getRow(fromRowIndex+rIdx);
			if(row==null) {
				row = sheet.createRow(fromRowIndex+rIdx);
			}
			for(int cIdx = 0; cIdx<rowData.length; cIdx++){
				Cell cell = row.getCell(fromColIndex+cIdx);
				if(cell==null){
					cell = row.createCell(fromColIndex+cIdx);
				}
				cell.setCellStyle(defaultStyles.get(cIdx));
				Object value = rowData[cIdx];
				setCellValue(cell, value);
			}
		}
	}
	public static void insertRows(Sheet sheet, int rowIndex, int rows) {
		if (sheet.getRow(rowIndex) != null) {  
            int lastRowNo = sheet.getLastRowNum();  
            sheet.shiftRows(rowIndex, lastRowNo, rows);  
        }
		for(int i=0;i<rows;i++){
			sheet.createRow(rowIndex++);  
		}
	}
	/**
	 * 合并单元格
	 * @param sheet
	 * @param firstRow
	 * @param lastRow
	 * @param firstCol
	 * @param lastCol
	 */
	public static void addMergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol){
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}
	private static void setCellValue(Cell cell, Object obj) {
		if (obj != null) {
			if (obj.getClass() == String.class) {
				cell.setCellValue((String) obj);
				cell.setCellType(Cell.CELL_TYPE_STRING);
			} else if (obj.getClass() == Double.class) {
				cell.setCellValue((Double) obj);
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			} else if (obj.getClass() == Date.class) {
				cell.setCellValue((Date) obj);
			} else if (obj.getClass() == Calendar.class) {
				cell.setCellValue((Calendar) obj);
			} else if (obj.getClass() == RichTextString.class) {
				cell.setCellValue((RichTextString) obj);
			} else {
				cell.setCellValue(obj.toString());
				cell.setCellType(Cell.CELL_TYPE_STRING);
			}
		}
	}
	private static void writeCell(Cell cell, Map<String, Object> formData){
		if(cell.getCellType()==Cell.CELL_TYPE_STRING){
			 String cellContent = cell.getStringCellValue();
			 if(isNotBlank(cellContent)){
				 //System.out.println(cellContent);
				 List<String> propNames = getPropNames(cellContent);
				 if(propNames.size()>0){
					 for(String prop : propNames){
						 Object val = formData.get(prop);
						 if(val==null){
							 val = "";
						 }
						 cellContent = cellContent.replace("${"+prop+"}", String.valueOf(val));
					 }
					 cell.setCellValue(cellContent);
				 }
			 }
		 }
	}
	private static boolean isNotBlank(String content){
		return content!=null && !content.trim().isEmpty();
	}
	@SuppressWarnings("unchecked")
	private static List<String> getPropNames(String cellContent){
		if(isNotBlank(cellContent) && cellContent.contains("$")){
			List<String> list = new ArrayList<String>();
			int start = 0;
			while(start < cellContent.length()){
				int beginIndex = cellContent.indexOf("${", start);
				if(beginIndex==-1){
					break;
				}
				int endIndex = cellContent.indexOf("}", beginIndex);
				list.add(cellContent.substring(beginIndex+2, endIndex));
				start = endIndex +1;
			}
			return list;
		}else{
			return Collections.EMPTY_LIST;
		}
	}
	
	
	public static void storeWorkbook(Workbook wb, File xlsxFile){
		OutputStream os = null;
        try {
        	os = new FileOutputStream(xlsxFile);
			wb.write(os);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}finally{
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	public static byte[] storeWorkbookToBytes(Workbook wb){
		ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
			wb.write(os);
			return os.toByteArray();
		} catch (IOException e) {
			throw new RuntimeException(e);
		}finally{
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
}
